﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Reflection;


namespace Common
{
    public class DbHelperSQL
    {
        #region 使用配置文件的连接字符串

        #region 设置连接
        //private static string conStr = PubConstant.ConString;//连接字符串

        //private static string _ConStr;

        //public static string ConStr
        //{
        //    get { return _ConStr; }
        //    set { _ConStr = value; }
        //}

        //private static SqlConnection _connection = new SqlConnection(_ConStr);

        ///// <summary>
        ///// 打开连接
        ///// </summary>
        ///// <returns></returns>
        //private static bool Open()
        //{
        //    try
        //    {
        //        _connection.Open();
        //        return true;
        //    }
        //    catch (Exception ex)
        //    {
        //        //("打开数据库出错:" + e.Message);

        //        return false;
        //    }
        //}

        ///// <summary>
        ///// 关闭连接
        ///// </summary>  
        ///// <returns></returns>
        //private static bool Close()
        //{
        //    try
        //    {
        //        _connection.Close();
        //        return true;
        //    }
        //    catch (Exception ex)
        //    {
        //        //("关闭数据库出错:" + e.Message);
        //        return false;
        //    }
        //}
        #endregion

        #region 方法

        ///// <summary>
        ///// 执行一条计算查询语句，返回单条记录（object类型）
        ///// </summary>
        ///// <param name="Sqlstr"></param>
        ///// <returns></returns>
        //public static object sqlExecuteScalar(string Sqlstr)
        //{

        //    try
        //    {
        //        using (SqlCommand cmd = new SqlCommand(Sqlstr, _connection))
        //        {
        //            Open();
        //            object obj = cmd.ExecuteScalar();
        //            if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
        //            {
        //                return null;
        //            }
        //            else
        //            {
        //                return obj;
        //            }
        //        }
        //    }

        //    catch (Exception ex)
        //    {
        //        throw ex;
        //    }

        //    finally
        //    {
        //        Close();
        //    }

        //}


        ///// <summary>
        ///// 执行一条计算查询语句,只返回影响行数（int类型）
        ///// </summary>
        ///// <param name="Sqlstr"></param>
        ///// <returns></returns>
        //public static int sqlExecuteNonQuery(string Sqlstr)
        //{

        //    try
        //    {

        //        using (SqlCommand cmd = new SqlCommand(Sqlstr, _connection))
        //        {
        //            Open();
        //            int rows = cmd.ExecuteNonQuery();
        //            return rows;
        //        }

        //    }
        //    catch (Exception ex)
        //    {
        //        throw ex;
        //    }
        //    finally
        //    {
        //        Close();
        //    }

        //}


        ///// <summary>
        ///// 返回数据集（DataSet类型）
        ///// </summary>
        ///// <param name="Sqlstr"></param>
        ///// <returns></returns>
        //public static DataSet ReturnDataSet(string Sqlstr)
        //{
        //    try
        //    {
        //        DataSet ds = new DataSet();
        //        Open();
        //        SqlDataAdapter cmd = new SqlDataAdapter(Sqlstr, _connection);
        //        cmd.Fill(ds, "ds");
        //        return ds;

        //    }
        //    catch (Exception ex)
        //    {
        //        throw ex;
        //    }
        //    finally
        //    {
        //        Close();
        //    }
        //}


        ///// <summary>
        ///// 返回List《string》
        ///// </summary>
        ///// <param name="Sqlstr">SQL语句</param>
        ///// <param name="Field">需要获取的字段</param>
        ///// <returns></returns>
        //public static List<string> ReturnStringList(string Sqlstr, string Field)
        //{
        //    try
        //    {
        //        List<string> list = new List<string>();
        //        using (SqlCommand cmd = new SqlCommand(Sqlstr, _connection))
        //        {
        //            Open();
        //            using (IDataReader dr = cmd.ExecuteReader())
        //            {
        //                while (dr.Read())
        //                {
        //                    string str = Convert.ToString(dr[Field]);
        //                    list.Add(str);
        //                }
        //            }
        //            return list;
        //        }


        //    }
        //    catch (Exception ex)
        //    {
        //        throw ex;
        //    }
        //    finally
        //    {
        //        Close();
        //    }
        //}

        #endregion

        #endregion
       

        

        /// <summary>
        /// 返回List《string》
        /// </summary>
        /// <param name="Sqlstr">SQL语句</param>
        /// <param name="Field">需要获取的字段</param>
        /// <returns></returns>
        public static List<string> ReturnStringList(SqlConnection con, string Sqlstr, string Field)
        {
            try
            {
                List<string> list = null;
                using (SqlCommand cmd = new SqlCommand(Sqlstr, con))
                {
                    con.Open();
                    using (IDataReader dr = cmd.ExecuteReader())
                    {
                        list = new List<string>();
                        while (dr.Read())
                        {
                            string str = Convert.ToString(dr[Field]);
                            list.Add(str);
                        }
                    }
                    return list;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (con != null)
                {
                    con.Close();
                }
            }
        }

        /// <summary>
        /// 从数据库返回一个对象List，使用此方法必须在SQL语句中将所有SELECT的字段设置的别名跟对象中的相应的属性名相同
        /// </summary>
        /// <typeparam name="T">对象</typeparam>
        /// <param name="con">SQL连接</param>
        /// <param name="Sqlstr">需要执行的命令</param>
        /// <returns></returns>
        public static List<T> ReturnObjectList<T>(SqlConnection con, string Sqlstr) where T:new()
        {
            try
            {
                List<T> list = new List<T>();
                using (SqlCommand cmd = new SqlCommand(Sqlstr, con))
                {
                    con.Open();
                    using (IDataReader dr = cmd.ExecuteReader())
                    {
                        list = new List<T>();
                        while (dr.Read())
                        {
                            T obj = new T();
                            foreach (PropertyInfo pi in obj.GetType().GetProperties())
                            {
                                pi.SetValue(obj, dr[pi.Name], null);
                            }
                            list.Add(obj);
                        }
                    }
                    return list;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (con != null)
                {
                    con.Close();
                }
            }
    
       }
        

    }
}
